package com.eorchis.module.planopinion.dao.impl;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.planopinion.bean.PlanOpinionExportBean;
import com.eorchis.module.planopinion.dao.IPlanOpinionDao;
import com.eorchis.module.planopinion.domain.PlanOpinion;
import com.eorchis.module.planopinion.ui.commond.PlanOpinionQueryCommond;
import com.eorchis.module.trainingplan.domain.TrainingPlan;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: PlanOpinionDaoImpl<br>
 * Description: 填写计划意见实体表数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author RongSL
 * @createDate 2016-07-12
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "main.java.com.eorchis.module.planopinion.dao.impl.PlanOpinionDaoImpl")
public class PlanOpinionDaoImpl
					extends HibernateAbstractBaseDao
					implements IPlanOpinionDao {
	
	public Class<? extends IBaseEntity> entityClass() {
		return PlanOpinion.class;
	}
	
	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		PlanOpinionQueryCommond planOpinionQueryCommond = (PlanOpinionQueryCommond)queryCommond;
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM PlanOpinion t");
		defaultQueryConditionBuilder.addCondition("t.opinionId", CompareType.IN, planOpinionQueryCommond.getSearchOpinionIds());
		defaultQueryConditionBuilder.addCondition("t.opinionId", CompareType.EQUAL,planOpinionQueryCommond.getSearchOpinionId());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
	}
	
	/**
	 * 查找下发表ID
	 * @author RongSL
	 * @Date 2016-7-21
	 * */
	@SuppressWarnings("rawtypes")
	@Override
	public String findIssuedId(PlanOpinionQueryCommond queryCommond){
		String id=null;
		String sql="select pl.Issued_ID from plan_Issued pl where pl.plan_id=:pid and pl.dept_id=:did ";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("pid", queryCommond.getSearchPlanId());
		params.put("did", queryCommond.getSearchDeptId());
		List list=this.executeFind(QueryStringType.SQL, sql, params);
		if(PropertyUtil.objectNotEmpty(list)){
			id=(String)list.get(0);
		}
		return id;
	}
	
	/**
	 * 意见汇总数据
	 * @author RongSL
	 * @Date 2016-07-21
	 * */
	public List<PlanOpinionExportBean> opinionSummary(PlanOpinionQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sql=new StringBuffer(" select tp.plan_id \"planId\",tp.class_name \"className\", (");
		List<?> list=getOpinionType();
		if(PropertyUtil.objectNotEmpty(list)){
			Iterator<?> ite=list.iterator();
			while(ite.hasNext()){
				Object []obj=(Object[]) ite.next();
				sql.append(" count(case po.opinion_title when '"+obj[0].toString().trim()+"' then 1 end)||','||");
			}
			sql=new StringBuffer(sql.substring(0, sql.length()-7)+") \"eachCountStr\","+
					"count(distinct po.issued_id) \"opinionCount\"," +
					"(select count(p.dept_id) from plan_Issued p where p.plan_id=tp.plan_id) \"issuedCount\" " +
					"from plan_Issued pi  " +
					"left join plan_Opinion po on po.issued_id = pi.issued_id " +
					"left join training_plan tp on pi.plan_id = tp.plan_id where 1=1 " );
//					"where tp.publish_state=:publicState " +
					if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
						sql.append(" and tp.plan_id in(:planIds )" );
						params.put("planIds", queryCommond.getSearchPlanIds());
					}
					if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptName())){
						sql.append(" and tp.undertake_name=:depName ");
						params.put("depName", queryCommond.getSearchDeptName());
					}
					sql.append(" group by tp.plan_id,tp.class_name,tp.create_date " +
					"order by tp.create_date desc");
		}
//		params.put("publicState", TrainingPlan.publicStatus_N);
		return (List<PlanOpinionExportBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params,PlanOpinionExportBean.class);
	}
	
	/**
	 * 意见明细数据
	 * @author RongSL
	 * @Date 2016-07-21
	 * */
	public List<PlanOpinionExportBean> opinionDetailed(PlanOpinionQueryCommond queryCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sql=new StringBuffer("select planId \"planId\", className \"className\",undertakeName \"undertakeName\"," +
				"jointName \"jointName\",planDay \"planDay\",applyNum \"applyNum\", " +
				"depName \"depName\", depId \"depId\",  nvl(feedbackDate,'未反馈') \"feedbackDateStr\", " +
				" issuedId \"issuedId\", opinionTitle \"opinionTitle\", opinionValue  \"opinionValue\" , " +
				"cc.start_plan_date \"startPlanDate\",cc.end_plan_date \"endPlanDate\" " +
				"from (select tp.plan_id planId,po.create_date createDate, tp.class_name className, " +
				"bd.depname depName,tp.start_plan_date,tp.end_plan_date, " +
				"bd.depid depId, to_char(po.create_date, 'yyyy-mm-dd hh24:mi:ss') feedbackDate,  tp.apply_num applyNum," +
				"tp.undertake_name undertakeName,  tp.joint_name jointName, tp.plan_day planDay, " +
				" po.opinion_id opinionId, pi.issued_id issuedId, (select p.opinion_title from plan_Opinion p " +
				"where p.opinion_id = po.opinion_id) opinionTitle,  (select p.opinion_value  from plan_Opinion p " +
				"where p.opinion_id = po.opinion_id) opinionValue  from training_plan tp left join plan_Issued pi on tp.plan_id = pi.plan_id  " +
				"left join plan_Opinion po on pi.issued_id = po.issued_id  left join base_department bd  on bd.depid = pi.dept_id " +
				" where 1=1 and tp.issued_state<>:issuedState ");
				if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptName())){
					sql.append(" and tp.undertake_name=:depName " );
					params.put("depName", queryCommond.getSearchDeptName());
				}
//				"and tp.plan_id in (select tpl.plan_id  from training_plan tpl where tpl.publish_state = 2) " +
				sql.append(" group by po.create_date,pi.dept_id, tp.plan_id,  tp.class_name,  bd.depname, po.opinion_id,tp.plan_day,tp.start_plan_date, " +
				"bd.depid, pi.issued_id, tp.apply_num,tp.undertake_name,tp.joint_name, tp.plan_day,tp.start_plan_date,tp.end_Plan_date  )cc where 1=1 ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanId())){
			sql.append(" and cc.planId=:planId ");
			params.put("planId", queryCommond.getSearchPlanId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getIssuedId())){
			sql.append(" and cc.issuedId=:issuedId ");
			params.put("issuedId", queryCommond.getIssuedId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getFeedbackDateStr())){
			sql.append(" and cc.feedbackDate=:feedbackDate ");
			params.put("feedbackDate", queryCommond.getFeedbackDateStr());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPlanIds())){
			sql.append(" and cc.planId in(:planIds )" );
			params.put("planIds", queryCommond.getSearchPlanIds());
		}
		params.put("issuedState", TrainingPlan.ISSUEDSTATE_W);
		sql.append("group by planId, className, depName,createDate, feedbackDate,planDay,jointName,cc.start_plan_date,cc.end_plan_date,undertakeName," +
				"applyNum, issuedId, opinionTitle, opinionValue, depId order by createDate desc nulls last,feedbackDate desc, depId ");
		return(List<PlanOpinionExportBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params,PlanOpinionExportBean.class);
	}
	
	/**
	 * 获得意见分类
	 * @author RongSL
	 * @Date 2016-07-22
	 * */
	public List<?> getOpinionType(){
		String qq=" select bb.DATA_CODE,bb.data_name from BASE_BASEDATA bb " +
				"left join BASE_BASEDATA_TYPE bbt on bb.type_id=bbt.type_id where bbt.type_code='fkyj' order by bb.order_num  ";
		return this.executeFind(QueryStringType.SQL, qq, null);
	}
	
	/**
	 * 更新下发状态
	 * @author RongSL
	 * @Date 2016-10-11
	 * */
	@Override
	public void updatePlanIssuedState(String iid){
		Map<String,Object> params=new HashMap<String,Object>();
		String sql="update plan_issued pi set pi.feedback_state=:state where pi.issued_id=:iid";
		params.put("state",TrainingPlan.DF_ISSUEDSTATE_Y);
		params.put("iid", iid);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
}
